package com.best.oasis.settlement.util;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * excel操作的相关工具类
 * @author BL00064
 *
 */
public class PoiUtil {
	/**
	 * 获取当前CELL内容
	 */
	public static String getCellText(HSSFCell cell) {
		if (cell == null)
			return "";
		int type = cell.getCellType();
		switch (type) {
		case HSSFCell.CELL_TYPE_BLANK:
			return "";
		case HSSFCell.CELL_TYPE_NUMERIC:
			Double value = cell.getNumericCellValue();
//			return Double.toString(value);
			BigDecimal big = new BigDecimal(value.toString());
			return big.toPlainString();
		case HSSFCell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString();
		}
		return "";
	}
	/**
	 * 构造标题格式
	 */
	public static HSSFCellStyle createHeaderStyle(HSSFWorkbook workbook){
	    HSSFCellStyle headerStyle = workbook.createCellStyle();
	    //设置字体格式 
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 11);//设置字体大小
        headerStyle.setFont(font);
        
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        
        return headerStyle;
	}
	/**
	 * 构造标题文字格式（有背景颜色，有边框）
	 */
	public static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook){
	    HSSFCellStyle headerStyle = workbook.createCellStyle();
	    //设置字体格式 
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 11);//设置字体大小
        headerStyle.setFont(font);
        
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);//浅绿
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        
        headerStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色  
        headerStyle.setBorderLeft((short) 1);// 边框的大小  
        headerStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色  
        headerStyle.setBorderRight((short) 1);// 边框的大小  
        
        headerStyle.setTopBorderColor(HSSFColor.BLACK.index);// 上边框的颜色  
        headerStyle.setBorderTop((short) 1);// 边框的大小  
        
        headerStyle.setBottomBorderColor(HSSFColor.BLACK.index);// 下边框的颜色  
        headerStyle.setBorderBottom((short) 1);// 边框的大小  
        
        return headerStyle;
	}
	/**
	 * 构造日期格式
	 */
	public static HSSFCellStyle createDateStyle(HSSFWorkbook workbook){
	    HSSFCellStyle dateStyle = workbook.createCellStyle();
	    short df=workbook.createDataFormat().getFormat("yyyy-mm-dd");  
	    dateStyle.setDataFormat(df);
        return dateStyle;
	}
	/**
	 * 构造合计栏货币格式
	 */
	public static HSSFCellStyle createFooterCurrencyStyle(HSSFWorkbook workbook){
		HSSFCellStyle currencyStyle = workbook.createCellStyle();
		short f = workbook.createDataFormat().getFormat( "###,##0.00" ); //为货币指定显示格式 
		currencyStyle.setDataFormat( f ); 
		//设置字体格式 
		HSSFFont font = workbook.createFont();
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    currencyStyle.setFont(font);
	    //设置背景
	    //currencyStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
		return currencyStyle;
	}
	/**
	 * 构造货币格式
	 */
	public static HSSFCellStyle createCurrencyStyle(HSSFWorkbook workbook){
		HSSFCellStyle currencyStyle = workbook.createCellStyle();
		short f = workbook.createDataFormat().getFormat( "###,##0.00" ); //为货币指定显示格式 
		currencyStyle.setDataFormat( f ); 
		return currencyStyle;
	}
	/**
	 * 建立工作簿
	 */
	public static HSSFSheet createSheet(HSSFWorkbook workbook,String sheetName){
		HSSFSheet sheet = workbook.createSheet(sheetName);
		sheet.setDefaultColumnWidth(20);
		return sheet;
	}
	public static HSSFSheet createSheet(HSSFWorkbook workbook){
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultColumnWidth(20);
		return sheet;
	}
	/**
	 * 构造标题
	 */	
	@SuppressWarnings("deprecation")
	public static HSSFRow createSheetTitleRow(HSSFWorkbook workbook,HSSFRow row,String[] headInfo){
		 //将列头数组转换为List
        List<String> headInfoList = new ArrayList<String>();
        headInfoList = Arrays.asList(headInfo);
        int columNum=headInfoList.size();
        //构造标题格式
	    HSSFCellStyle headerStyle = createTitleStyle(workbook);
	    for(int i=0;i<columNum;i++)   
	    {   
            HSSFCell cellHead = row.createCell(i);   
            cellHead.setCellValue(headInfoList.get(i).toString());
            cellHead.setCellStyle(headerStyle);
	    }
		return row;
	}
	/**
	 * 设置文本
	 * @param needData
	 * @param value
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static void setStringValue( String value,HSSFCell cell){
		
		cell.setCellType( HSSFCell.CELL_TYPE_STRING);
		
		if( value == null || StringUtil.isBlank( value ) ){
			cell.setCellValue("");
		}else{
			cell.setCellValue(value);
		}
	}
	
	/**
	 * 设置日期
	 * @param needData
	 * @param value
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static void setDateValue( Date value,HSSFCell cell,HSSFCellStyle cellStyle){
		if( value == null ){
			cell.setCellValue("");
		}else{
			cell.setCellValue( value );
		}
		if( cellStyle != null ){
			cell.setCellStyle(cellStyle);
		}
	}
	/**
	 * 设置金额的数值
	 * @param needData 是否显示数据
	 * @param value
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static void setCurrencyValue(BigDecimal value,HSSFCell cell,HSSFCellStyle cellStyle){
		
		if( value == null ){
			cell.setCellValue("");
		}else{
			cell.setCellValue(value.doubleValue());
		}
		if( cellStyle != null ){
			cell.setCellStyle(cellStyle);
		}
	}
	/**
	 * 获取合计行等函数所在的列
	 * @param colIndex
	 * @return
	 */
	public static  String getColLetter(int colIndex)
	{
	   String ch = "";
		if (colIndex < 26) {
			ch = "" + (char) ((colIndex) + 65);
		} else {
			ch = "" + (char) ((colIndex) / 26 + 65 - 1)
					+ (char) ((colIndex) % 26 + 65);
		}
	     return ch;
	}
}
